﻿Public Class frmElevesLettrer
    Private szSQL As String
    Private rs As ADODB.Recordset
    Dim pNoLigne As Integer
    Dim solde As Decimal = 0
    Dim Lettre As Integer
    Dim nb As Integer = 0

    Private Sub frmElevesLettrer_Load(sender As Object, e As System.EventArgs) Handles Me.Load

        CB_TOUS.SelectedIndex = 0
        BO_DELETTRER.Enabled = False
        BO_LETTRER.Enabled = False
        TE_SOLDE.Clear()
        ComptesToScreen()

    End Sub

    Private Sub ComptesToScreen()

        ' effacer la listview
        LV_COMPTE.Items.Clear()
        nb = 0

        ' Lire les comptes de l'élève
        szSQL = "CALL spGet_ELEVES_CPT_byLETTRAGE(" & gCD_CLI & "," & CB_TOUS.SelectedIndex & ")"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        Do While Not rs.EOF
            LV_COMPTE.Items.Add(rs.Fields("no_ligne").Value)
            LV_COMPTE.Items(nb).SubItems.Add(rs.Fields("date").Value)
            LV_COMPTE.Items(nb).SubItems.Add(nv(rs.Fields("intitule").Value))
            LV_COMPTE.Items(nb).SubItems.Add(Zero2Space(rs.Fields("debit").Value))
            LV_COMPTE.Items(nb).SubItems.Add(Zero2Space(rs.Fields("credit").Value))
            If rs.Fields("mode_regl").Value Then
                LV_COMPTE.Items(nb).SubItems.Add(Mid(getLibelleByCode("P_MODE_REGL", rs.Fields("mode_regl").Value), 1, 3))
            Else
                LV_COMPTE.Items(nb).SubItems.Add("")
            End If
            LV_COMPTE.Items(nb).SubItems.Add(IfDate(rs.Fields("date_valeur").Value))
            If rs.Fields("date_valeur").Value.ToString.Length Then
                LV_COMPTE.Items(nb).ForeColor = Color.Green
            End If
            LV_COMPTE.Items(nb).SubItems.Add(rs.Fields("mtval").Value)
            LV_COMPTE.Items(nb).SubItems.Add(Zero2Space(rs.Fields("lettre").Value))
            rs.MoveNext()
            nb = nb + 1
        Loop
        ' Close the recordset AND connection.
        rs.Close()

    End Sub

    Private Sub LV_COMPTE_Click(sender As Object, e As System.EventArgs) Handles LV_COMPTE.Click

        ' calcul du solde des lignes sélectionner
        solde = 0

        For Each lvItem As ListViewItem In LV_COMPTE.SelectedItems
            ' ligne déjà lettrée ?
            If lvItem.SubItems(8).Text.Length > 0 Then
                Lettre = lvItem.SubItems(8).Text
                If lvItem.SubItems(4).Text.Length > 0 Then
                    solde = solde + CDec(lvItem.SubItems(4).Text)
                End If
                If lvItem.SubItems(3).Text.Length > 0 Then
                    solde = solde - CDec(lvItem.SubItems(3).Text)
                End If
                TE_SOLDE.Text = solde.ToString
                BO_DELETTRER.Enabled = True
                BO_LETTRER.Enabled = False
                Exit Sub
            Else
                If lvItem.SubItems(4).Text.Length > 0 Then
                    solde = solde + CDec(lvItem.SubItems(4).Text)
                End If
                If lvItem.SubItems(3).Text.Length > 0 Then
                    solde = solde - CDec(lvItem.SubItems(3).Text)
                End If
                BO_DELETTRER.Enabled = False
            End If
        Next
        TE_SOLDE.Text = solde.ToString
        If solde < 0 Then
            TE_SOLDE.ForeColor = Color.Red
        Else
            TE_SOLDE.ForeColor = Color.Blue
        End If

        If solde = 0 Then
            BO_LETTRER.Enabled = True
        Else
            BO_LETTRER.Enabled = False
        End If
    End Sub

    Private Sub BO_DELETTRER_Click(sender As Object, e As System.EventArgs) Handles BO_DELETTRER.Click

        If MessageBox.Show("Etes-vous sûr de vouloir délettrer la lettre " & Lettre & " ?", "Confirmation du délettrage", _
                    MessageBoxButtons.OKCancel, MessageBoxIcon.Question) = Windows.Forms.DialogResult.OK Then
            For i = 0 To LV_COMPTE.Items.Count - 1
                If LV_COMPTE.Items.Item(i).SubItems(8).Text() = Lettre.ToString Then
                    LV_COMPTE.Items.Item(i).SubItems(8).Text = ""
                    ScreenToComptes(LV_COMPTE.Items.Item(i).SubItems(0).Text, 0)
                End If
            Next
        End If

    End Sub

    Private Sub BO_LETTRER_Click(sender As Object, e As System.EventArgs) Handles BO_LETTRER.Click

        Dim LettreMax As Integer = 0

        If MessageBox.Show("Etes-vous sûr de vouloir lettrer les lignes sélectionnées ?", "Confirmation du lettrage", _
                    MessageBoxButtons.OKCancel, MessageBoxIcon.Question) = Windows.Forms.DialogResult.OK Then
            ' lire Lettre Max
            szSQL = "SELECT MAX(lettre) as maxlettre FROM eleves_cpt WHERE cd_cli=" & gCD_CLI
            GetRecordset(ctDSN_AEM, szSQL, rs)
            If rs.RecordCount > 0 Then
                LettreMax = rs.Fields("maxlettre").Value
            End If
            LettreMax += 1
            For Each lvItem As ListViewItem In LV_COMPTE.SelectedItems
                lvItem.SubItems(8).Text = LettreMax
                ScreenToComptes(lvItem.SubItems(0).Text, LettreMax)
            Next
        End If

    End Sub

    Private Sub ScreenToComptes(ByVal pNo_ligne As Integer, ByVal pLettre As Integer)

        ' maj la ligne dans la table ELEVES_CPT
        szSQL = "UPDATE eleves_cpt SET lettre = " & pLettre & " WHERE no_ligne = " & pNo_ligne
        ExecQuery(ctDSN_AEM, szSQL)

    End Sub

    Private Sub CB_TOUS_SelectedIndexChanged(sender As Object, e As System.EventArgs) Handles CB_TOUS.SelectedIndexChanged
        ComptesToScreen()
    End Sub

    Private Sub BO_LETTRER_AUTO_Click(sender As System.Object, e As System.EventArgs) Handles BO_LETTRER_AUTO.Click

        ' Lettrage automatique
        Me.Cursor = Cursors.WaitCursor
        ' Lettrage automatique des écritures à cette date
        szSQL = "CALL spUPD_ELEVES_LETTRER(" & gCD_CLI & ")"
        ExecQuery(ctDSN_AEM, szSQL)
        Me.Cursor = Cursors.Default
        ' réafficher le compte
        ComptesToScreen()
    End Sub
End Class